﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Today_YeJi_G_Sort]
	(
		@CompanyId VARCHAR(500),
		@Today nchar(10),
		@sort VARCHAR(3000) 
		--@StartDate NCHAR(10),
		--@EndDate NCHAR(10)
	)
AS
BEGIN
	DECLARE @strSql nvarchar(max),@sWhere nvarchar(max)
	
	set @sWhere=''
if @CompanyId<>'0'
begin
	set @sWhere=' Where Id in ('+@CompanyId+') '
end 
	
SET @strSql='
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, '''+@Today + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@Today + ' 23:59:59'')	
	;WITH list As(Select *,ROW_NUMBER() OVER (ORDER BY '+@sort+' )AS Row from (select c.Id,dwbh,c.CompanyName,Mid
	,ISNULL((Select Sum(SalePrice-CostPrice) From Project Where TypeId=1 and CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq AND ProductId not IN (22,29,33) AND NOE_Flag=0),0)+
		ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.CompanyId=c.Id and Order_Change_Hedge.oid =o.oid and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=c.Id AND o.NOE_Flag=0 ),0) As IAA
	,ISNULL((Select Sum(SalePrice-CostPrice) From Project Where TypeId=2 and CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq AND ProductId not IN (22,29,33) AND NOE_Flag=0),0)+
		ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.CompanyId=c.Id and UpTime>=@sRq and UpTime<=@eRq and Order_Change_Hedge.oid =o.oid and o.companyId=c.Id AND o.NOE_Flag=0  ),0) As IAB
	,ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=1),0)+ 
		ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=c.Id and UpTime>=@sRq and UpTime<=@eRq and o.companyId=c.Id AND o.NOE_Flag=1),0) as qqqdfqzcml
	,ISNULL((Select Sum(h_YingShou) From [Order] Where CompanyId=c.Id and financerenlingdate >=@sRq and financerenlingdate<=@eRq AND StateId>=1 AND (NOE_Flag<>2)),0)+
		ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.CompanyId=c.Id and UpTime>=@sRq and UpTime<=@eRq and Order_Change_Hedge.oid =o.oid and o.companyId=c.Id AND o.NOE_Flag<>2),0) As IAC
	,ISNULL((Select Sum(CostPrice) From Project Where CompanyId =c.Id and CreateDate>=@sRq and CreateDate<=@eRq and FinanceAudit=1),0)+ISNULL((SELECT sum(dch_yf) FROM Order_Change_Hedge WHERE CompanyId=c.Id and UpTime>=@sRq and UpTime<=@eRq),0)+
		ISNULL((Select Sum(CostPrice) From Project Where CompanyId =c.Id and FinanceAudit=2 and CreateDate>=@sRq and CreateDate<=@eRq and FinanceAuditDate>@eRq),0) As IAD
	,ISNULL((Select Sum(F_YingShou) From [Order] Where CompanyId=c.Id and financerenlingdate >=@sRq and financerenlingdate<=@eRq AND StateId>=1 AND (NOE_Flag<>2)),0)+
		ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.CompanyId=c.Id and UpTime>=@sRq and UpTime<=@eRq  and Order_Change_Hedge.oid =o.oid and o.companyId=c.Id AND o.NOE_Flag<>2),0)+
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=3 and CreateDate>=@sRq and CreateDate<=@eRq  and OrderType in(1,2)),0) As IAE
	,ISNULL((Select Sum(Prices) From Order_PaymentRecord inner join [Order] o on SWId=2 and Order_PaymentRecord.CompanyId=c.Id and FinanceDate >=@sRq and FinanceDate<=@eRq  and Order_PaymentRecord.oid =o.oid and o.companyId=c.Id AND o.NOE_Flag<>2),0)+
		ISNULL((SELECT sum(dch_wk) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.CompanyId=c.Id and UpTime>=@sRq and UpTime<=@eRq and Order_Change_Hedge.oid =o.oid and o.companyId=c.Id AND o.NOE_Flag<>2),0) As IAF
	,ISNULL((SELECT SUM(income) FROM Finance_RunningAccount_Non WHERE CompanyId=c.Id AND CreateDate>=@sRq and CreateDate<=@eRq),0)+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=2 and CreateDate>=@sRq and CreateDate<=@eRq),0) As IAG
	From Company c '+@sWhere+') as nb
	)
	Select * From list,
	(SELECT SUM(IAA) AS IAA_a,SUM(IAB) AS IAB_a,sum(qqqdfqzcml) as qqqdfqzcml_a,SUM(IAC) AS IAC_a,SUM(IAD) AS IAD_a,SUM(IAE) AS IAE_a,SUM(IAF) AS IAF_a,SUM(IAG) AS IAG_a
	 FROM list) AS n ORDER BY row'

	 EXEC (@strSql)
End
	RETURN
